package com.gxwzxy.medic.dao.Impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.gxwzxy.medic.dao.UserDao;
import com.gxwzxy.medic.entity.User;
import com.gxwzxy.medic.entity.otherentity.UserRole;
import com.gxwzxy.medic.until.Connect;

public class UserDaoImpl implements UserDao {

	// 澹版槑杩炴帴鍙橀噺
	Connection conn = null;
	ResultSet rs = null;
	PreparedStatement ps = null;

	/**
	 *  鏌ヨ鎵�鏈夌敤鎴�
	 */
	@Override
	public List<User> findUser() {
		// 澹版槑鍙橀噺
		ArrayList<User> au = new ArrayList<>();
		String sql = null;
		try {
			// 鑾峰彇杩炴帴
			conn = Connect.getConnection();
			// 鍒涘缓Sql璇彞
			sql = "select * from user";
			// 鍒涘缓SQL鍛戒护瀵硅薄
			ps = conn.prepareStatement(sql);
			// 鎵цsql璇彞
			rs = ps.executeQuery();
			// 鑾峰彇缁撴灉
			while (rs.next()) {
				User u = new User(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5));
				au.add(u);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Connect.close(conn, ps, rs);
		}
		// 鍏抽棴杩炴帴
		return au;
	}

	/**
	 * 鏌ヨ鐢ㄦ埛鐨勪釜鏁�
	 */
	@Override
	public int findUserSize() {
		String sql = "select COUNT(*) from user";
		int i = 0;
		try {
			//璋冪敤闈欐�佹柟娉�
			rs = Connect.executeF(sql);
			if(rs.next()) {
				i = rs.getInt(1);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			Connect.close(conn, ps, rs);
		}
		return i;
	}

	/**
	 * 鍒嗛〉鏌ヨ锛宻tart鏄紑濮嬬殑椤垫暟锛宻ize鏄竴椤佃鏈夊灏戞暟鎹�
	 */
	@Override
	public List<User> findUserIndex(int start, int size) {
		// 澹版槑鍙橀噺
		ArrayList<User> au = new ArrayList<>();
		String sql = null;
		try {
			// 鍒涘缓Sql璇彞
			sql = "select * from user limit ?,?";
			// 鎵цsql璇彞
			rs =Connect.executeF(sql, start,size);
			// 鑾峰彇缁撴灉
			while (rs.next()) {
				User u = new User(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5));
				au.add(u);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Connect.close(conn, ps, rs);
		}
		// 鍏抽棴杩炴帴
		return au;
	}

	/**
	 * 鐢ㄦ埛鍒犻櫎
	 */
	@Override
	public boolean UserDelete(String userId) {
		//澹版槑鍙橀噺
		String sql = null;
		String rsql = null;
		int i = -1;
		int j = -1;
		sql = "delete from user where user_id = ?";
		i = Connect.executeDML(sql, userId);
		rsql = "delete from user_role where user_id = ?";
		j = Connect.executeDML(rsql, userId);
		if(i!=-1&&j!=-1) {
			return true;
		}else {
			return false;
		}
	}

	/**
	 * 鏌ヨ鐢ㄦ埛缂栧彿鏄惁宸茬粡瀛樺湪
	 */
	@Override
	public boolean findUserId(String userId) {
		//澹版槑boolean
		boolean bl = false;
		//澹版槑SQL
		String sql = "select COUNT(*) from user where user_id = ?";
		//澶勭悊寮傚父
		try {
			rs = Connect.executeF(sql, userId);
			while(rs.next()) {
				//璐﹀彿鑻ュ瓨鍦紝灏哹l鍙樹负true
				if(rs.getInt(1)>0) {
					bl =true;
				}
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			Connect.close(conn, ps, rs);
		}
		return bl;
	}

	/**
	 * 鏌ヨ鐢ㄦ埛璐﹀彿鏄惁宸茬粡瀛樺湪
	 */
	@Override
	public boolean findUserName(String userName) {
		//澹版槑boolean
		boolean bl = false;
		//澹版槑SQL
		String sql = "select COUNT(*) from user where user_name = ?";
		//澶勭悊寮傚父
		try {
			rs = Connect.executeF(sql, userName);
			while(rs.next()) {
				//璐﹀彿鑻ュ瓨鍦紝灏哹l鍙樹负true
				if(rs.getInt(1)>0) {
					bl =true;
				}
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			Connect.close(conn, ps, rs);
		}
		return bl;
	}

	/**
	 * 鍒涘缓鐢ㄦ埛
	 */
	@Override
	public boolean saveUser(User u, String roleId) {
		//局部变量
		PreparedStatement prepar = null;
		String sql = null;
		String usql = null;
		int i = 0;
		int j = 0;
		//获取数据库连接
		conn = Connect.getConnection();
		//插入语句sql
		sql = "insert into user(user_name,password,name,user_id) value(?,?,?,?)";
		usql = "insert into user_role(user_id,role_id) value(?,?)";
		try {
			//开启事务，因为需要对sql的占位符进行赋值
			conn.setAutoCommit(false);
			//获取数据库会话声明
			ps = conn.prepareStatement(sql);
			prepar = conn.prepareStatement(usql);
			//对sql语句的占位符进行赋值
			ps.setString(1, u.getUserName());
			ps.setString(2, u.getPassword());
			ps.setString(3, u.getName());
			ps.setString(4, u.getUserId());
			prepar.setString(1, u.getUserId());
			prepar.setString(2, roleId);
			//执行sql
			i = ps.executeUpdate();
		    j = prepar.executeUpdate();
			//提交事务
			conn.commit();
		}catch(Exception e) {
			try {
				//发生异常时候进行回滚
				conn.rollback();
			}catch(Exception e1) {
				e1.printStackTrace();
			}
		}finally {
			Connect.closedouble(conn, ps);
			try {
				prepar.close();
			}catch(Exception e2) {
				e2.printStackTrace();
			}
		}
		if(i>0&&j>0) {
			return true;
		}else {
			return false;
		}
	}

	/**
	 * 閫氳繃鐢ㄦ埛缂栧彿userId鏌ヨ鐢ㄦ埛淇℃伅
	 */
	@Override
	public List<UserRole> findUserById(String userId) {
		//澹版槑鍙橀噺
		String sql = "select u.user_id,u.user_name,u.name,r.role_id from user u,user_role r where u.user_id = ? and r.user_id = ?";
		ArrayList<UserRole> au = new ArrayList<UserRole>();
		try {
			//璋冪敤闈欐�佹柟娉�
			rs = Connect.executeF(sql, userId,userId);
			while(rs.next()) {
				UserRole ur = new UserRole(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4));
				au.add(ur);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			Connect.close(conn, ps, rs);
		}
		return au;
	}
	
	/**
	 * 閫氳繃鐢ㄦ埛缂栧彿userId淇敼鐢ㄦ埛淇℃伅
	 */
	@Override
	public boolean updateUser(User user, String roleId) {
		//定义全部的局部变量
		String sql = null;
		String rsql = null;
		PreparedStatement prepar = null;
		int i = 0;
		int j = 0;
		//获取数据库连接
		conn = Connect.getConnection();
		try {
			//构造sql语句
			sql = "update user set name = ?,user_name = ? where user_id = ?";
			rsql = "update user_role r set r.role_id = ? where r.user_id = ?";
			//开启事务
			conn.setAutoCommit(false);
			//获得SQL语句会话
			ps = conn.prepareStatement(sql);
			prepar = conn.prepareStatement(rsql);
			//填充占位符的数据
			ps.setString(1, user.getName());
			ps.setString(2, user.getUserName());
			ps.setString(3, user.getUserId());
			prepar.setString(1, roleId);
			prepar.setString(2, user.getUserId());
			//执行sql更新操作
			i = ps.executeUpdate();
			j = prepar.executeUpdate();
			//事务提交
			conn.commit();
		}catch(Exception e) {
			e.printStackTrace();
			try {
				//发生异常的时候事务全部回滚
				conn.rollback();
			}catch(Exception e1) {
				e1.printStackTrace();
			}
		}finally {
			Connect.closedouble(conn, ps);
			try {
				prepar.close();
			}catch(Exception e2) {
				e2.printStackTrace();
			}
		}
		//当两个sql语句都执行完毕返回true
		if(i>0&&j>0) {
			return true;
		}else {
			return false;
		}
	}
	
	public static void main(String[] args) {
		User u = new User(11,"19008","221","222","2223");
		String r = "2";
		UserDaoImpl ud = new UserDaoImpl();
		ud.updateUser(u, r);
	}
	
}
